﻿using EasyCode.Entity;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EasyCode.DbHelper.MySql
{
    public class MySqlHelper : IDbHelper
    {
        private MySqlConnection GetConnection(DataSourceEntity dataSourceEntity)
        {
            String connectionString = "Data Source=" + dataSourceEntity.IP
                                    + ";Port=" + dataSourceEntity.Port
                                    + ";Initial Catalog=information_schema"
                                    + ";user id=" + dataSourceEntity.UserName
                                    + ";password=" + dataSourceEntity.Password
                                    + ";";
            MySqlConnection con = new MySqlConnection(connectionString);
            return con;
        }

        public DataTable GetDatabases(DataSourceEntity dataSourceEntity)
        {
            String sqlString = "SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA`";
            DataTable rs = new DataTable();
           
            MySqlConnection con = GetConnection(dataSourceEntity);
            con.Open();
            MySqlCommand cmd = new MySqlCommand(sqlString, con);
            cmd.CommandType = CommandType.Text;
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            da.Fill(rs);

            con.Close();
            return rs;
        }

        public DataTable GetTables(DataSourceEntity dataSourceEntity)
        {
            String sqlString = @"   SELECT `TABLE_NAME`
                                    FROM   `TABLES`
                                    WHERE  `TABLE_SCHEMA` = '{0}'";
            sqlString = String.Format(sqlString, dataSourceEntity.DbName);
            DataTable rs = new DataTable();

            MySqlConnection con = GetConnection(dataSourceEntity);
            con.Open();
            MySqlCommand cmd = new MySqlCommand(sqlString, con);
            cmd.CommandType = CommandType.Text;
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            da.Fill(rs);

            con.Close();
            return rs;
        }

        public DataTable GetFilds(DataSourceEntity dataSourceEntity, string tableName)
        {
            String sqlString = @"   select ORDINAL_POSITION,
                                    DATA_TYPE,
                                    COLUMN_NAME,
                                    COLUMN_COMMENT,
	                                case COLUMN_KEY
	                                    when 'PRI' then 'Y'
	                                    else ''
	                                end as COLUMN_KEY,
                                    CHARACTER_MAXIMUM_LENGTH

                                    from information_schema.columns
                                    where TABLE_SCHEMA='{0}' and table_Name = '{1}'
                                    order by ORDINAL_POSITION ";
            sqlString = String.Format(sqlString, dataSourceEntity.DbName, tableName);
            DataTable rs = new DataTable();

            MySqlConnection con = GetConnection(dataSourceEntity);
            con.Open();
            MySqlCommand cmd = new MySqlCommand(sqlString, con);
            cmd.CommandType = CommandType.Text;
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            da.Fill(rs);

            con.Close();
            return rs;
        }

    }
}
